#!/usr/bin/env python27
# -*- coding: utf-8 -*-
# @author xuhualei
# @date 2023.08.02

import os, sys
from datetime import datetime, timedelta

############ 数据质量相关slot ############
def read_data(dt):
    sql="""
    select dt,case when biz='gj' then 'wangya' when biz='wb' then 'niuqian01' end as owner_id,*
    from
    hdp_lbg_supin_zplisting.app_zp_n_n_flow_monitor_kanban
    where dt='%s' and detail_exposure_rate_diff_7d<-0.05 and detail_exposure_cnt_diff_7d>100;
    """ % (dt)
    os.system('hive -e "'+str(sql)+'">./result1.txt')
    results = []
    f=open("./result1.txt","r")
    for line in f:
        rows=line.split('\t')
        results.append(rows)
    return results

if __name__=="__main__":
    yesterday=datetime.now() - timedelta(1)
    dt=datetime.strftime(yesterday, '%Y%m%d')
    results = read_data(dt)
    for rows in results:
        header='日期:%s, 姓名:%s'% (rows[0],rows[1])
        title1='<td>业务线</td><td>slot</td><td>slot名称(T)</td><td>七日平均详情量</td><td>当日详情量</td><td>七日平均串联量</td><td>当日串联量</td><td>七日平均串联率</td><td>当日串联率</td><td>当日串联与七日平均串联的差值</td><td>当日串联与七日平均串联的差异率</td>'
        content1='<td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td>' % (rows[2],rows[3],rows[4],rows[5],rows[6],rows[7],rows[8],rows[9],rows[10],rows[11],rows[12])
        username=str(rows[1])
        receiver1='%s@58.com' % (username)

        content = """
            <h2>58/赶集数据质量相关slot</h2>
            <h3>%s</h3>
            <table border='1' cellspacing='0' cellpadding='7px'>
            <tr style='font-weight: bold' bgcolor='#dcdcdc'>%s</tr>
            <tr>%s</tr>
            </table>
            <h2>相关链接:</h2>
            <a href='https://wuba.xinghuo.58.com/report/visualization?dashboard_id=19240'>点击串联率分slot波动</a>
            <p>负责人：lixiyong01, xuhualei, wanglizhou</p>
            """ %(header, title1, content1)
        print content
        cmdFmt="""curl -X POST -d \"oaname=%s&subject=58/赶集数据质量相关slot&content=%s&token=d28dd236b6f6e89e4145882e4d227d9e\" http://openmsg.monitor.op.58dns.org/channel/email
        """ %(receiver1,content)
        print cmdFmt
        os.system(cmdFmt)
